#include "database.h"
#include <QVector>
#include <QString>

DataBase *DataBase::self = nullptr;

DataBase::DataBase()
{
    qDebug()<<QSqlDatabase::drivers();
    database=QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName("acc_paw.db");
    database.setHostName("192.168.1.16");
    database.setUserName("root");
    database.setPassword("root");
    database.setPort(4466);
    bool ok=database.open();

    if(ok){
        qDebug()<<"connect is ok";
    }
    else{
        qDebug()<<"connect is fail";
    }

    QSqlQuery query(database);
    QString s="create table qq_clients (account varchar(20) primary key, password varchar(20),name varchar(20),pic_path varchar,signature varchar);";
    if(IsTableExist("qq_clients")){
        qDebug()<<s<<"表已存在，不需要再创建新的"<<endl;
    }
    else {
        if(query.exec(s))
        {
            qDebug()<<"创建表成功"<<endl;
        }
        else
            qDebug()<<"创建表失败"<<endl;
    }
}

DataBase::~DataBase(){
    database.close();
}

//用于登录时检查账号和密码是否正确
bool DataBase::Isacc_pawRight(const QString&acc,const QString&paw){
    QSqlQuery query(database);
    //输出数据库中已存有的账号信息
    qDebug()<<"---query all data end---"<<endl;
    QString sq1="SELECT account,password,name FROM qq_clients";
    query.exec(sq1);
    while(query.next()){
        qDebug()<<QString("account:%1,password:%2")
                  .arg(query.value("account").toString())
                  .arg(query.value("password").toString());
    }
    QString str=QString("select * from qq_clients where account='%0' and password ='%1';" )
            .arg(acc).arg(paw);
    query.exec(str);
    if(query.first()){
        selfaccount=acc;
        selfpassword=paw;
        return true;
    }
    else
        return false;
}

//用于注册时插入新用户
void DataBase::insertUser(const QString &acc,const QString &paw,const QString&nam){

    QString S =QString("insert into qq_clients (account,password,name,pic_path,signature) VALUES('%1','%2','%3','%4','%5');")
            .arg(acc,paw,nam).arg(":/new/pre/resource/head/222.jpg","hi QT!");
    QSqlQuery query(database);
    if(query.exec(S))
    {
        qDebug()<<"注册执行插入操作"<<endl;
    }
    else
        qDebug()<<"注册未执行插入操作"<<endl;
}

//检查表是否存在
bool DataBase::IsTableExist( QString table){
    QSqlQuery query(database);
    QString sq1=QString("select * from sqlite_master where type='table' and name ='%1';").arg(table);
    query.exec(sq1);
    if(query.first()){
        qDebug()<<"tab"<<table<<"存在"<<endl;
        return true;
    }
    else
    {
        qDebug()<<"tab"<<table<<"不存在"<<endl;
        return false;
    }
}

//创建新的表
void DataBase::create_New_Table(const QString&str){
    QSqlQuery query(database);
    QString s=QString ("create table '%1' (account varchar(20) primary key, password varchar(20),name varchar(20),pic_path varchar,signature varchar);").arg(str);
    if(IsTableExist(str)){
        qDebug()<<"表已存在，不需要再创建新的"<<endl;
    }
    else {
        if(query.exec(s))
        {
            qDebug()<<"创建成功"<<str<<endl;
        }
        else
            qDebug()<<"创建失败"<<str<<endl;
    }
}

//检测账号是否已存在，用于注册
bool DataBase::isAccountExit(const QString &tab, const QString &acc){
    QSqlQuery query(database);
    database.setDatabaseName("acc_paw.db");
    database.setHostName("192.168.1.16");
    database.setUserName("root");
    database.setPassword("root");
    database.setPort(4466);

    QString s=QString("select * from '%1' where account= '%2';").arg(tab).arg(acc);
    query.exec(s);
    if(query.first()){
        qDebug()<<"Account Exit"<<endl;
        return true;
    }
    else{
        qDebug()<<"Account NOT Exit"<<endl;
        return false;
    }
}

//获取自身信息
QString DataBase::getSelfAccount(){
    return selfaccount;
}

QString DataBase::getSelfPassword(QString s){
    QSqlQuery query(database);
    query.exec("SELECT password FROM qq_clients WHERE account= "+s);
    query.next();
    selfpassword=query.value("password").toString();
    return selfpassword;
}

QString DataBase::getSelfName(QString s){
    QSqlQuery query(database);
    query.exec("SELECT name FROM qq_clients WHERE account= "+s);
    query.next();
    selfname=query.value("name").toString();
    return selfname;
}

QString DataBase::getSelfPic_Path(QString s){
    QSqlQuery query(database);
    query.exec("SELECT pic_path FROM qq_clients WHERE account= "+s);
    query.next();
    selfpic_path=query.value("pic_path").toString();
    return selfpic_path;
}

QString DataBase::getSelfSig(QString s){
    QSqlQuery query(database);
    query.exec("SELECT signature FROM qq_clients WHERE account= "+s);
    query.next();
    selfsig=query.value("signature").toString();
    return selfsig;
}

//检查自定义表中是否存在某一账号
bool DataBase::isPeopleExit(const QString &str,const QString &tab){
    QString s=QString("SELECT * FROM '%1' WHERE account like '%2' or name like '%3';").arg(tab).arg("%"+str+"%").arg("%"+str+"%");
    QSqlQuery query(database);
    query.exec(s);
    if(query.first()){
        qDebug()<<s<<endl;
        qDebug()<<"查询结果存在"<<endl;
        return true;
    }
    else{
        qDebug()<<s<<endl;
        //  qDebug()<<"查询结果不存在"<<endl;
        return false;
    }
}

void DataBase::show_search_rsl( const QString &tab,QVector<QString>&search_rsl){
    qDebug()<<"show_research_rsl "<<endl;
    QSqlQuery query(database);
    QString s=QString("SELECT * FROM '%1' WHERE account").arg(tab);
    query.exec(s);
    //尾插
    while(query.next()){
        qDebug()<<QString("account:%1,password:%2")
                  .arg(query.value("account").toString())
                  .arg(query.value("password").toString());
        QString n=query.value("pic_path").toString();
        search_rsl.push_back(query.value("account").toString());
        search_rsl.push_back(query.value("password").toString());
        search_rsl.push_back(query.value("name").toString());
        search_rsl.push_back(query.value("pic_path").toString());
        search_rsl.push_back(query.value("signature").toString());
    }
}

//将新的好友插入到该用户的好友表中 用于添加好友
void DataBase::insertFriend(const QString &tab, const QString &acc, const QString &paw, const QString &nam,const QString&pic,const QString&sig){
    qDebug()<<"tab="<<tab<<endl;
    QSqlQuery query(database);
    if(database.open()){
        qDebug()<<"insertFriend connect is ok";
    }
    else{
        qDebug()<<"insertFriend connect is fail";
    }

    QString S =QString("insert into '%1' (account,password,name,pic_path,signature) VALUES('%2','%3','%4','%5','%6');")
            .arg(tab).arg(acc,paw,nam,pic,sig);
    qDebug()<<"---query all data end---"<<endl;
    QString sq1="SELECT account,password,name FROM 1";
    query.exec(sq1);
    while(query.next()){
        qDebug()<<QString("account:%1,password:%2")
                  .arg(query.value("account").toString())
                  .arg(query.value("password").toString());
    }
    if(IsTableExist(tab)){
        if(query.exec(S))
        {
            qDebug()<<"表已建好 插入新朋友执行插入操作"<<endl;
        }
        else
            qDebug()<<"表已建好 插入新朋友未执行插入操作"<<endl;
    }
    else{
        create_New_Table(tab);
        if(query.exec(S))
        {
            qDebug()<<"表没有建好  插入新朋友执行插入操作"<<endl;
        }
        else
            qDebug()<<"表没有建好  插入新朋友未执行插入操作"<<endl;
    }
}

void DataBase::changeselfinformation(const QString &myacc, const QString &mynam, const QString &mypic, const QString &mysig){
    QSqlQuery query(database);

    QString s1="UPDATE qq_clients SET name='"+mynam+"' WHERE account='"+myacc+"';";
    QString s2="UPDATE qq_clients SET pic_path='"+mypic+"' WHERE account='"+myacc+"';";
    QString s3="UPDATE qq_clients SET signature='"+mysig+"' WHERE account='"+myacc+"';";
    query.exec(s1);
    query.exec(s2);
    query.exec(s3);

    QString s4=QString("SELECT * FROM '%1' WHERE account").arg(myacc);
    query.exec(s4);

    QVector<QString> v1;
    while(query.next()){
        v1.push_back(query.value("account").toString());
    }
    for(int i=0;i<v1.size();i++){
        QSqlQuery q1(database);
        QString tab=v1[i];
        QString s1="UPDATE '"+tab+"' SET name='"+mynam+"' WHERE account='"+myacc+"';";
        QString s2="UPDATE '"+tab+"' SET pic_path='"+mypic+"' WHERE account='"+myacc+"';";
        QString s3="UPDATE '"+tab+"' SET signature='"+mysig+"' WHERE account='"+myacc+"';";
        q1.exec(s1);
        q1.exec(s2);
        q1.exec(s3);

    }

}
